"""
修复数据库字段问题：
1. notes.metadata -> notes.extra_metadata
2. 扩展 chat_sessions.knowledge_base_id 长度（支持多个知识库）
"""
import sys
import os

# 添加项目根目录到Python路径
sys.path.insert(0, os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))

from sqlalchemy import create_engine, text
from app.core.config import settings
import logging

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)


def fix_database_columns():
    """修复数据库字段"""
    engine = create_engine(settings.DATABASE_URL)
    
    with engine.connect() as conn:
        # 修复 1: notes 表的 metadata 字段改名为 extra_metadata
        try:
            logger.info("检查 notes 表是否存在 metadata 字段...")
            result = conn.execute(text("""
                SELECT column_name 
                FROM information_schema.columns 
                WHERE table_name = 'notes' AND column_name = 'metadata'
            """))
            
            if result.fetchone():
                logger.info("将 notes.metadata 重命名为 extra_metadata...")
                conn.execute(text("ALTER TABLE notes RENAME COLUMN metadata TO extra_metadata"))
                logger.info("✅ notes.metadata -> extra_metadata 修改成功")
            else:
                logger.info("notes 表已使用 extra_metadata 字段，无需修改")
        except Exception as e:
            logger.warning(f"修改 notes.metadata 失败（可能已修改过）: {e}")
        
        # 修复 2: 扩展 chat_sessions.knowledge_base_id 字段长度
        try:
            logger.info("扩展 chat_sessions.knowledge_base_id 字段长度...")
            conn.execute(text("""
                ALTER TABLE chat_sessions 
                ALTER COLUMN knowledge_base_id TYPE VARCHAR(255)
            """))
            logger.info("✅ chat_sessions.knowledge_base_id 长度已扩展到 255")
        except Exception as e:
            logger.warning(f"修改 chat_sessions.knowledge_base_id 失败（可能已修改过）: {e}")
        
        conn.commit()
        logger.info("\n✅ 所有字段修复完成！")


def verify_fixes():
    """验证修复结果"""
    engine = create_engine(settings.DATABASE_URL)
    
    with engine.connect() as conn:
        # 检查 notes 表
        logger.info("\n检查 notes 表字段:")
        result = conn.execute(text("""
            SELECT column_name, data_type, character_maximum_length 
            FROM information_schema.columns 
            WHERE table_name = 'notes' AND column_name IN ('metadata', 'extra_metadata')
        """))
        for row in result:
            logger.info(f"  {row[0]}: {row[1]} ({row[2]})")
        
        # 检查 chat_sessions 表
        logger.info("\n检查 chat_sessions 表字段:")
        result = conn.execute(text("""
            SELECT column_name, data_type, character_maximum_length 
            FROM information_schema.columns 
            WHERE table_name = 'chat_sessions' AND column_name = 'knowledge_base_id'
        """))
        for row in result:
            logger.info(f"  {row[0]}: {row[1]} ({row[2]})")


if __name__ == "__main__":
    try:
        logger.info("开始修复数据库字段...")
        fix_database_columns()
        verify_fixes()
        logger.info("\n🎉 数据库修复完成，请重启后端服务！")
    except Exception as e:
        logger.error(f"❌ 修复失败: {str(e)}")
        import traceback
        traceback.print_exc()
        sys.exit(1)

